package Test;

import java.sql.*;

public class dbHelper {

    // 添加数据库配置信息
    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    static final String DATABASE_URL ="jdbc:mysql://localhost:3306/jdbc";
    static String username="root";
    static String password="123456";

    // 常用对象
    static Connection conn = null;
    static PreparedStatement preparedStatement = null;
    static Statement statement = null;
    static ResultSet resultSet = null;

    /**
     *  连接数据库
     */
    public static Connection getConn(){

        try {
            // 1.加载驱动
            Class.forName(JDBC_DRIVER);
            // 2.使用DriverManager创建连接
            conn  = DriverManager.getConnection(DATABASE_URL,username,password);
        } catch (Exception e){
            e.printStackTrace();
        }
        return conn;
    }

    /**
     *  进行查询
     */
    public static void select(String sql){
        try {
            conn = getConn();
            // 通过conn创建statement，帮助执行sql语句
            statement = conn.createStatement();
            // 执行sql语句存入结果集
            resultSet = statement.executeQuery(sql);
            System.out.println(resultSet);
            // 输出查询结果
            System.out.println("id\t|name\t|sex\t|email\t\t\t|birthday");
            // 遍历结果集获取表记录
            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String sex = resultSet.getString("sex");
                String email = resultSet.getString("email");
                Date birthday = resultSet.getDate("birthday");
                // 输出结果
                System.out.println(id+"\t|"+name+"\t|"+sex+"\t\t|"+email+"\t|"+birthday);
            }

        } catch (Exception e){
            e.printStackTrace();
        }
    }

    /**
     *  关闭资源
     */
    public static void close(){
        if (conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (preparedStatement!=null){
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement!=null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


}
